During this section we're going to take a look at importing and exporting data into and outside of Microsoft
Excel.
So the first one I want take a look at here deals with importing data into Excel.
And then we're going to take a look at perhaps one of the most common file types to import into an Excel
document.
This is a text file.
Now there are many different types of text files that other systems maybe Crystal Reports Microsoft
Access or some other application would export into some type of text file.
Now I want to be able to turn around and take that text file that some other system export ID and bring
it into my Excel environment.
What we're going to take a look at here is known as a tab delimited text file.
Another very common type of text file is a c v file or a comma separated document.
So first there is an example file of a text file.
You can download this from lecturn number two of this course that contains a tab delimited text file
.
I'm going to use that one to import into this blank Excel document that I have open in front of you
right now and this is super simple to do.
Not very many steps.
Take a look.
So I'm going to hop up to my Data menu.
Top of the screen.
I'm going to go into the section called Get External Data.
This is your import commands or your import portion of Microsoft Excel.
Now there are a number different places we can import data from.
We're going to take a look at these.
First we're going to take a look at from tax.
It's I mean it's like from text mezzotint on my browser window and I've been taken right into my exercise
files.
You'll have you'll need to navigate to wherever your text files your example files are located at.
But the one that's a part of lecture 2 that you can download so you can practice this is called Excel
one or two exercises and it's a t s t file and you can see it's a text document.
So I'm just like that one.
I'll hit import.
And this is going to take me through the text import wizard and it's really simple as can kind of take
my hand walk me walk me through a few steps three little steps to build it.
Import this text file into Excel.
So first off with the text file there's two different types.
You can do a delimited text file meaning that each column you can see down below is a sample of my data
like you see I have a year column a month column type column a salesperson column and so on.
And right now it's all kind of smash together.
Right now I can't can't really distinguish very clearly what's one column versus another.
Now you can have a delimited file where there's some special character that separates each column like
a tab or comma or some other character.
Or you can do fixed with meaning that each colon has a very defined fixed width.
In this first column as an inch the second column is half an inch and so on.
So mine is delimited.
It's known as a tab delimited document.
And I'm going to say my data does have headers year month type and so on.
I mean hit next.
Now I want to know how is the data actually delimited How is it separated.
Well mine is tab.
But you do have a few other ones just based on your document and how your data is actually delimited
or separated.
Do you see with tab selected that I do get clear distinct columns in there if I switch it from tab that
all smashed together to something else like comma.
Well if there's commas in there it'll try to separate it but that's not a consistent delimiter across
all my data so I won't do commas I mean do tab hit next your next step.
You could do a little bit of tweaking with the data itself right now.
By default everything that gets important it in will be marked as general.
So Excel will then take over and dictate what it should be.
Oh this is a date.
This is numeric.
This is currency and so on.
I'm just going to leave it said the General but if you like you can select columns down below and specify
between text and date.
You can even skip columns as well.
I don't even region.
Turn that one off.
So you could do just that just a little bit of tweaking nothing big.
So I'm going to stick with just general across the board this is my defaults.
I'll hit OK and now wants to know.
Well where do you want to put the data.
I'm just going to put it in the existing worksheet there instead of a one hit OK.
And I've now got my data imported from a simple tab delimited text file.
Nothing big a nice little wizard.
I just went to my dad a tab from text walk me through three little steps.
Find the document explain to excel how your data is separated how the columns are broken up.
Mine was tab.
Yours might be comma.
Whatever it is and that's it you know important.
A simple text file into Excel.
So try this out.
I've supplied the txt file back inside a lecture to make sure you download that.
Open up a blate document in Excel.
Visit your data tab do from tax go find that document and imported it